/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       InitMngTablesRunner.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/19      feiwe                         add
 */


package com.thh.core.staup;

import cn.hutool.core.io.resource.ResourceUtil;
import com.alibaba.fastjson2.JSON;
import com.thh.common.enums.DatabaseDriverEnums;
import com.thh.common.utils.StringUtils;
import com.thh.common.utils.sql.SqlUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.StringTokenizer;

/**
 * 初始化管理的表
 */
@Component
@Order(1)
public class InitTablesRunner implements CommandLineRunner {
    private final Logger logger = LoggerFactory.getLogger(InitTablesRunner.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Environment env;

    private final String[] sqlFileList = {"quartz.sql", "tmd_20220622.sql", "tmd_database.sql"};

    private final String checkTable = "gen_table_column";

    @Override
    public void run(String... args) throws Exception {
        initSql();
    }


    public void initSql() throws Exception {
        createTableAndIndex();
    }

    private void createTableAndIndex() throws SQLException {
        String driverClassName = env.getProperty("spring.datasource.driver-class-name");
        DatabaseDriverEnums databaseDriverEnums = DatabaseDriverEnums.getByDriverClassName(driverClassName);
        if (checkExistTable(databaseDriverEnums)) {
            return;
        }
        for (String sqlFile : sqlFileList) {
            String sqlList = ResourceUtil.readUtf8Str("initSql/"  + databaseDriverEnums.getName()  +"/" +sqlFile);
            runSqlForCreate(sqlList);
        }
    }

    private boolean checkExistTable(DatabaseDriverEnums databaseDriverEnums) {
        String checkSql = databaseDriverEnums.getCheckSql();
        switch (databaseDriverEnums){
            case MYSQL:
                checkSql = String.format(checkSql, checkTable);
                break;
            case ORACLE:
                String userName = env.getProperty("spring.datasource.username");
                checkSql = String.format(checkSql, userName.toUpperCase(), checkTable.toUpperCase());
                break;
            case H2:
                checkSql = String.format(checkSql, checkTable.toLowerCase());
                break;
        }
        List tableList = jdbcTemplate.queryForList(checkSql);
        if (tableList.size() == 0) {
            return false;
        }
        return true;
    }

    private void createMysqlTableAndIndex(String tableName) throws Exception {
        String checkTable = "show tables like '" + tableName + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(JSON.toJSONString(tableList));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/mysql/" + tableName + ".sql");
            runSqlForCreate(sqlList);
        }
    }

    private void createPostgreSqlTableAndIndex(String tableName) throws SQLException {
        String checkTable = " select tablename from pg_tables where schemaname = 'public' and tablename='" + tableName + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(JSON.toJSONString(tableList));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/postgresql/" + tableName + ".sql");
            runSqlForCreate(sqlList);
        }
    }

    private void createOracleTableAndIndex(String tableName) throws Exception {
        String userName = env.getProperty("spring.datasource.username");
        String checkTable = "select table_name from all_tables where owner='" + userName.toUpperCase() + "' and table_name = '" + tableName.toUpperCase() + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(JSON.toJSONString(tableList));
        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String sqlList = ResourceUtil.readUtf8Str("tables/oracle/" + tableName + ".sql");
            runSqlForCreate(sqlList);
            if (!"mdms_ver".equals(tableName)) {
                //oracle的trigger要单独建
                String tiggerSql = "create or replace trigger " + tableName + "\n" +
                        "  before update on " + tableName + "\n" +
                        "  for each row\n" +
                        "  begin\n" +
                        "     :new.gmt_modified :=sysdate;\n" +
                        "end " + tableName + ";";
                jdbcTemplate.execute(tiggerSql);
            }
        }
    }

    private void createH2TableAndIndex(String tableName) throws Exception {
        String checkTable = "select table_name from information_schema.tables where table_schema='PUBLIC' and table_name = '" + tableName.toUpperCase() + "'";
        List tableList = jdbcTemplate.queryForList(checkTable);
        logger.debug(JSON.toJSONString(tableList));

        /*表不存在时就创建表*/
        if (tableList.size() == 0) {
            String result = ResourceUtil.readUtf8Str("tables/h2/" + tableName + ".sql");
            runSqlForCreate(result);
        }

    }


    @Transactional(rollbackFor = Exception.class)
    void runSqlForCreate(String sqlList) {
        logger.debug("sqlList:" + sqlList);
        StringTokenizer stn = new StringTokenizer(sqlList, ";");
        while (stn.hasMoreTokens()) {
            String sql = stn.nextToken();
            if (StringUtils.isBlank(sql)) {
                continue;
            }
            logger.info(" jdbcTemplate.execute  regex before, sql:{}", sql);
            // 正则表达式， 去掉sql注释
            sql = SqlUtil.removeComments(sql);
            logger.info(" jdbcTemplate.execute  regex after, sql:{}", sql);
            if (StringUtils.isBlank(sql)) {
                continue;
            }
            try {
                jdbcTemplate.execute(sql);
            }catch (Exception e){
                logger.error(" jdbcTemplate.execute error, sql:{}", sql);
                throw e;
            }
        }
    }

    private void execute(String sql) throws SQLException {
        TransactionSynchronizationManager.initSynchronization();
        DataSource dataSource = jdbcTemplate.getDataSource();
        Connection connection = DataSourceUtils.getConnection(dataSource);
        connection.setAutoCommit(false);
        boolean autoCommit = connection.getAutoCommit();
        try {
            jdbcTemplate.execute(sql);
            connection.commit();
        }catch (SQLException e){
            connection.rollback();
        }finally {
            connection.setAutoCommit(autoCommit);
            if(connection != null){
                connection.close();
            }
        }
    }
}
